package admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

import test.CSV_Handler;
import test.DB_Builder;


;

public class LocalDB {

	SERVER_TYPE			SERVER_PROFILE;
	static AUDIT_CODE	AUDITCODE;

	final String		userStatus_ACTIVE	= "ACTIVE";
	final String		userStatus_INACTIVE	= "INACTIVE";
	final String		userGroup_INTERNAL	= "SPA";
	final String		userGroup_EXTERNAL	= "EXT";
	final String		userCLEARANCE_EXT	= "5";
	final String		userCLEARANCE_INT	= "";

	String				CSVFile				= "CSV";		// "MONITOR"

	public LocalDB(SERVER_TYPE SERVER_PROFILE) {
		this.SERVER_PROFILE = SERVER_PROFILE;
	}

	public boolean Audit_User_Access2CSV(String csvBaseFP) {

		/*
		 * Generate User Access record CSV based on Audit trails
		 * Audit Field CODE_EVENT, DATE_EVENT, AUDIT_ID ID_OBJECT, TYPE_OBJECT
		 * ID_OTHER_OBJECT1, ID_OTHER_OBJECT2 ID_USER TEXT_VALUE1, TEXT_VALUE2
		 * ID_SUBSYSTEM, ID_FILE, ID_DATABASE, DATE_ARRIVE
		 * DATE FORMAT = "dd/mm/yyyy"
		 */

		final String START_DATE = "1/4/2011";
		final String BEFORE_DATE = "12/4/2011";

		DB_Builder db = new DB_Builder();
		System.out.println("Audit Trail");
		System.out.println("START_DATE : " + START_DATE);
		System.out.println("BEFORE_DATE : " + BEFORE_DATE);

		try {
			// String query = "SELECT COUNT(*) FROM DBOWNER.audit_trail" ;
			String query = " SELECT ID_USER, DATE_EVENT, CODE_EVENT "+
			" FROM DBOWNER.audit_trail"+
			" WHERE "+
			" (CODE_EVENT =    '" + AUDIT_CODE.LOGIN.getEventCode() + "'"+
			" OR CODE_EVENT =  '" + AUDIT_CODE.LOGOUT.getEventCode() + "')"+
			" AND DATE_EVENT >= (to_date('" + START_DATE  + "', 'dd/mm/yyyy'))"+
			" AND DATE_EVENT <  (to_date('" + BEFORE_DATE + "', 'dd/mm/yyyy'))";

			System.out.println(query);
			query = query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner());

			db.connect(SERVER_PROFILE);
			ResultSet rs = db.execute(query);

			CSV_Handler CSV_file = new CSV_Handler(csvBaseFP, "LoginAudit", "|", CSVFile);

			CSV_file.AddTitleFields("Time|User|EVENT");
			while (rs.next()) {
				CSV_file.AddElement(rs.getString(1));
				CSV_file.AddElement(rs.getString(2));
				CSV_file.AddElement(rs.getString(3));
				CSV_file.NextLine();
			}
			CSV_file.WriteAll();
			db.disconnect();

		} catch (SQLException se) {
			se.printStackTrace();
			System.exit(1);
		}

		return true;
	}

	private int count_drawing(String beginDate, String endDate, String docType) {

		int result = 0;
		// SELECT COUNT(*) AS DRAWING_COUNT_TRANS FROM &OWNER.documents WHERE
		// &docType_TRAN AND &DATE_RANGE;

		String query =
			" SELECT COUNT(*) FROM DBOWNER.documents" +
			" WHERE " + docType +
			" AND DATE_CREATE >= (to_date('" + beginDate + "', 'dd/mm/yyyy'))" +
			" AND DATE_CREATE <  (to_date('" + endDate + "', 'dd/mm/yyyy'))";

		try {
			DB_Builder db = new DB_Builder();
			db.connect(SERVER_PROFILE);
			ResultSet rs = db.execute(query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner()));

			while (rs.next()) {
				// userCount = rs_new.getString(1);
				result = rs.getInt(1);
			}
			db.disconnect();
		} catch (SQLException se) {

			se.printStackTrace();
			System.exit(1);
		}
		return result;
	}

	public boolean countTotalDrawingNumber() {

		DB_Builder db = new DB_Builder();
		System.out.println("Total Count of Drawings");

		try {
			db.connect(SERVER_PROFILE);

			for (DrawingType each : DrawingType.values()) {
				String query = "SELECT count(*) FROM DBOWNER.documents WHERE " + each.getSQL();
				ResultSet rs = db.execute(query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner()));
				while (rs.next())
					System.out.println(each.name() + ": " + rs.getString(1));
				/*
				 * Integer i = Integer.parseInt(rs.getString(1));
				 * NumberFormat formatter = new DecimalFormat("#,###,###,###");
				 * System.out.println(formatter.format(i));
				 */

			}

			db.disconnect();

		} catch (SQLException se) {
			se.printStackTrace();
			System.exit(1);
		}
		return true;
	}

	private String getDateString(String inDateString, long delta) {
		/*
		 * delta - (long) ( 1000 * 60 * 60 * 24 ) inDateString =
		 * "5 Dec 1999 00:07:21"
		 */
		SimpleDateFormat inDate_formatter = new SimpleDateFormat("dd-MM-yyyy hh:mm:ss", Locale.ENGLISH);
		SimpleDateFormat outDate_formatter = new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");

		ParsePosition pos = new ParsePosition(0);
		Date baseTime = inDate_formatter.parse(inDateString, pos);

		Date date_modified = new Date(baseTime.getTime() + (long) (1000 * 60 * 60 * 24));
		return outDate_formatter.format(date_modified);
	}

	public boolean listDrawingCountPerMonth2CSV(String csvBaseFP) {

		CSV_Handler CSV_file = new CSV_Handler(csvBaseFP, "Drawing Count", "|", CSVFile);
		CSV_file.AddTitleFields("Begin|End|" + "Trans|Dist|SUB-T|Raster|EMAIL|TECHDOC");

		String beginDate = "";
		String endDate = "";

		for (Integer year = 2011; year <= 2011; year++) {
			for (Integer month = 1; month <= 12; month++) {
				beginDate = "1/" + month + "/" + year;

				if ((month + 1) == 13)
					endDate = "1/1/" + Integer.toString(year + 1);
				else
					endDate = "1/" + Integer.toString(month + 1) + "/" + Integer.toString(year);

				System.out.println("Month: " + month);

				String oldDateFormat = "dd/MM/yyyy";
				String newDateFormat = "dd/MMM/yyyy";

				beginDate = test.DateTools.convertDateTxt2Txt(beginDate, oldDateFormat, newDateFormat);
				endDate = test.DateTools.convertDateTxt2Txt(endDate, oldDateFormat, newDateFormat);

				CSV_file.AddElement(beginDate);
				CSV_file.AddElement(endDate);

				for (DrawingType each : DrawingType.values())
					CSV_file.AddElement("" + count_drawing(beginDate, endDate, each.getSQL()));

				CSV_file.NextLine();
			}
		}
		CSV_file.WriteAll();
		return true;

	}

	public boolean listAllUser2CSV(String csvBaseFP) {

		/*
		 * Generate list of users to CSV
		 * 		SELECT NAME,TEXT_NAME_EMAIL, TEXT_USER_SIGNON,
		 * 		ID_PARENT,ASSIGNED_PRIVILEGES, ID_CLEARANCE, ID_CLASSIFICATION,
		 * 		DATE_CREATE,DATE_UPDATE,DATE_DELETE, DATE_INACTIVATE, DATE_DISABLE,ID_USER_SET_DISABLE
		 * 		FROM &OWNER.users"
		 * DATE FORMAT = "dd/mm/yyyy"
		 */

		DB_Builder db = new DB_Builder();
		System.out.println("List Users ");

		try {
			String query = "SELECT NAME,TEXT_NAME_EMAIL, TEXT_USER_SIGNON, " +
			"ID_PARENT, ID_CLEARANCE, ID_CLASSIFICATION, " +
			"DATE_CREATE, DATE_UPDATE, DATE_INACTIVATE, DATE_DISABLE " +
			"FROM DBOWNER.users " +
			"ORDER BY DATE_CREATE ";

			query = query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner());

			db.connect(SERVER_PROFILE);
			ResultSet rs = db.execute(query);

			CSV_Handler CSV_file = new CSV_Handler(csvBaseFP, "ListUsers", "|", CSVFile);

			final String fieldTitles = "SELECT NAME|EMAIL|UserID|" +
			"ID_PARENT|ID_CLEARANCE|ID_CLASSIFICATION|" +
			"DATE_CREATE|DATE_UPDATE|DATE_INACTIVATE|DATE_DISABLE";

			CSV_file.AddTitleFields(fieldTitles);
			while (rs.next()) {
				CSV_file.AddElement(rs.getString(1));
				CSV_file.AddElement(rs.getString(2));
				CSV_file.AddElement(rs.getString(3));
				CSV_file.AddElement(rs.getString(4));
				CSV_file.AddElement(rs.getString(5));
				CSV_file.AddElement(rs.getString(6));
				CSV_file.AddElement(rs.getString(7));
				CSV_file.AddElement(rs.getString(8));
				CSV_file.AddElement(rs.getString(9));
				CSV_file.AddElement(rs.getString(10));
				CSV_file.NextLine();
			}
			CSV_file.WriteAll();
			db.disconnect();

		} catch (SQLException se) {
			se.printStackTrace();
			System.exit(1);
		}

		return true;
	}


	public boolean list_user_count_per_month2CSV(String csvBaseFP) {

		CSV_Handler CSV_file = new CSV_Handler(csvBaseFP, "USER_COUNT", "|", CSVFile);

		final String fieldTitles = "Begin|End|New Users (SPA)|Inact Users (SPA)|"+
		"New Users (EXT)|Inact Users (EXT)";

		CSV_file.AddTitleFields(fieldTitles);

		for (Integer year = 2005; year <= 2011; year++) {
			for (Integer month = 1; month <= 12; month++) {

				String beginDate = "1/" + month + "/" + year;
				String endDate;

				if ((month + 1) == 13)
					endDate = "1/1/" + Integer.toString(year + 1);
				else
					endDate = "1/" + Integer.toString(month + 1) + "/" + Integer.toString(year);

				Integer userCount_INT_ACTIVE = list_users_count(beginDate, endDate, userStatus_ACTIVE,
						userCLEARANCE_INT);
				Integer userCount_EXT_ACTIVE = list_users_count(beginDate, endDate, userStatus_ACTIVE,
						userCLEARANCE_EXT);
				Integer userCount_INT_INACTIVE = list_users_count(beginDate, endDate, userStatus_INACTIVE,
						userCLEARANCE_INT);
				Integer userCount_EXT_INACTIVE = list_users_count(beginDate, endDate, userStatus_INACTIVE,
						userCLEARANCE_EXT);

				String oldDateFormat = "dd/MM/yyyy";
				String newDateFormat = "dd/MMM/yyyy";

				beginDate = test.DateTools.convertDateTxt2Txt(beginDate, oldDateFormat, newDateFormat);
				endDate = test.DateTools.convertDateTxt2Txt(endDate, oldDateFormat, newDateFormat);

				CSV_file.AddElement(beginDate);
				CSV_file.AddElement(endDate);
				CSV_file.AddElement(userCount_INT_ACTIVE.toString());
				CSV_file.AddElement(userCount_INT_INACTIVE.toString());
				CSV_file.AddElement(userCount_EXT_ACTIVE.toString());
				CSV_file.AddElement(userCount_EXT_INACTIVE.toString());

				CSV_file.NextLine();
			}
		}
		CSV_file.WriteAll();
		return true;
	}

	private Integer list_users_count(String beginDate, String endDate, String userState, String userClearance) {

		Integer userCount = 0;

		try {
			DB_Builder db = new DB_Builder();
			db.connect(SERVER_PROFILE);
			String query = "";
			if (userState == userStatus_ACTIVE) {
				query = query +
				" SELECT COUNT(*) " +
				" FROM DBOWNER.users" +
				" WHERE" +
				" DATE_CREATE   >= (to_date('" + beginDate + "','dd/mm/yyyy'))" +
				" AND DATE_CREATE <(to_date('" + endDate + "',  'dd/mm/yyyy'))";
			}
			else {
				query = query +
				" SELECT COUNT(*)" +
				" FROM DBOWNER.users" +
				" WHERE DATE_INACTIVATE >= 	(to_date('" + beginDate + "','dd/mm/yyyy'))" +
				" AND DATE_INACTIVATE   <	(to_date('" + endDate + "',  'dd/mm/yyyy'))";
			}

			if (userClearance == userCLEARANCE_EXT)
				query = query + " AND ID_CLEARANCE = " + userClearance;

			if (userClearance == userCLEARANCE_INT)
				query = query + " AND ID_CLEARANCE <> 5";

			ResultSet rs = db.execute(query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner()));

			while (rs.next()) {
				// userCount = rs_new.getString(1);
				userCount = rs.getInt(1);
			}
			db.disconnect();
		} catch (SQLException se) {

			se.printStackTrace();
			System.exit(1);
		}
		return userCount;
	}

	public boolean total_docVersion_count() {

		DB_Builder db = new DB_Builder();
		System.out.println("Total Count of Drawing Versions");

		try {
			db.connect(SERVER_PROFILE);

			for (DrawingType each : DrawingType.values()) {
				String query =
					" SELECT COUNT(*) FROM DBOWNER.doc_versions " +
					" WHERE ID_DOC in " +
					" (SELECT ID_OBJECT FROM DBOWNER.documents WHERE " + each.getSQL() + ")";
				ResultSet rs = db.execute(query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner()));

				while (rs.next())
					System.out.println(each.name() + ": " + rs.getString(1));
			}

			/*
			 * for (DocType each : docTypeList) { String query =
			 * "SELECT COUNT(*) FROM DBOWNER.doc_versions " +
			 * "WHERE ID_DOC in (SELECT ID_OBJECT FROM DBOWNER.documents WHERE "
			 * + (each.sql) + ")"; ResultSet rs = db
			 * .execute(query.replaceAll("DBOWNER", db_owner)); while
			 * (rs.next()) System.out.println(each.name + ": " +
			 * rs.getString(1)); }
			 */

			db.disconnect();

		} catch (SQLException se) {
			se.printStackTrace();
			System.exit(1);
		}

		return true;
	}

	public boolean total_users_count() {

		DB_Builder db = new DB_Builder();

		System.out.println("Total Count of Drawings");

		try {
			db.connect(SERVER_PROFILE);

			String query = "SELECT COUNT(*) FROM DBOWNER.users";
			query = query.replaceAll("DBOWNER", SERVER_PROFILE.getDbOwner());
			ResultSet rs = db.execute(query);

			while (rs.next())
				System.out.println(rs.getString(1));
			db.disconnect();

		} catch (SQLException se) {
			se.printStackTrace();
			System.exit(1);
		}

		return true;
	}
}

/*
 * EXTRA CODES
 * OjiTypeDefn doc_type = (OjiTypeDefn ) apiSession.getObject("duotA15");
 * doc.setTypeDefinition(doc_type ); doc.getFieldValue("Drawing Contents",
 * "Admin");
 */
